﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace ChinaDynastyTours.Controls
{
    class StatisticsCtrl
    {

        #region 业务涨跌比分析 
        public static DataTable BusinessTotalAdultNumAnalysis(string customerCode)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"   SELECT sum(AdultNum)as 人数,datepart(year,[StartDate]) as 年份
                                            FROM orgt_tourgroup 
                                            WHERE CustomerCode={0} AND TourgroupState<>'{1}' AND TourgroupState<>'{2}' GROUP BY datepart(year,[StartDate])",customerCode, TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public static DataTable BusinessBudgeTotalPriceAndProfitAnalysis(string customerCode)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"   SELECT SUM(BudgeTotalPrice)as 审核价格,SUM(BudgeTotalPrice)-SUM(BudgeTotalCost) as 审核利润 ,datepart(year,[StartDate]) as 年份
                                            FROM orgt_tourgroup 
                                            WHERE CustomerCode={0} AND TourgroupState<>'{1}' AND TourgroupState<>'{2}' GROUP BY datepart(year,[StartDate])", customerCode, TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
//        public static DataTable BusinessBudgeTotalProfitAnalysis(string customerCode)
//        {
//            Database db = DatabaseFactory.CreateDatabase();

//            DbCommand dbCommand;

//            string Sql = string.Format(@"   SELECT sum(AdultNum)as 人数,datepart(year,[StartDate]) as 年份
//                                            FROM orgt_tourgroup 
//                                            WHERE CustomerCode={0} AND TourgroupState<>'{1}' AND TourgroupState<>'{2}' GROUP BY datepart(year,[StartDate])", customerCode, TeamStateEnum.取消, TeamStateEnum.删除);

//            dbCommand = db.GetSqlStringCommand(Sql);

//            DataSet ds = db.ExecuteDataSet(dbCommand);
//            if (ds.Tables.Count > 0)
//                return ds.Tables[0];
//            else
//                return null;
//        }
        #endregion

        #region 国别分析

        /// <summary>
        /// 国别分析
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public static DataTable CountryAnalysis(DateTime startDate, DateTime endDate)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"   SELECT countrycode as 国家代码,country as 国家名称, SUM(AdultNum) as 人数,COUNT(customercode)as 团数,SUM(SaleTotalCost)as 预算金额,SUM(BudgeTotalPrice)as 审核金额,SUM(RealTotalPrice) as 实收金额,SUM(BudgeTotalPrice)-SUM(RealTotalPrice) as 欠款,SUM(BudgeTotalPrice)-SUM(BudgeTotalCost) as 利润 
                                            FROM orgt_tourgroup 
                                            WHERE StartDate BETWEEN '{0}' AND '{1}' AND TourgroupState<>'{2}' AND TourgroupState<>'{3}' GROUP BY country,countrycode", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable CountryAnalysisDetails(string countryCode, DateTime startDate, DateTime endDate)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"   SELECT CountryCode ,Tourgroupname ,TourgroupID ,Country ,AdultNum ,SaleTotalCost , BudgeTotalPrice ,RealTotalPrice ,(BudgeTotalPrice-RealTotalPrice) as ownPrice,TourgroupState FROM orgt_tourgroup
                                            WHERE CountryCode='{0}' and StartDate BETWEEN '{1}' AND '{2}' AND TourgroupState<>'{3}' AND TourgroupState<>'{4}' ", countryCode, startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

        #region 客户分析

        public static DataTable DisperseAnalysis(DateTime startDate, DateTime endDate)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //总数统计
            string Sql = string.Format(@"   SELECT customercode as 客户代码,customername as 客户名称, SUM(AdultNum) as 总人数,COUNT(customercode)as 总团数
                                            FROM orgt_tourgroup 
                                            WHERE StartDate BETWEEN '{0}' AND '{1}' AND TourgroupState<>'{2}' AND TourgroupState<>'{3}' GROUP BY customername,customercode", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);
            dbCommand = db.GetSqlStringCommand(Sql);
            DataSet ds1 = db.ExecuteDataSet(dbCommand);

            //大于10人以上的团
            Sql = string.Format(@"   SELECT customercode as 客户代码,COUNT(customercode)as 大于10人团数
                                            FROM orgt_tourgroup 
                                            WHERE AdultNum>=10 AND StartDate BETWEEN '{0}' AND '{1}' AND TourgroupState<>'{2}' AND TourgroupState<>'{3}' GROUP BY customername,customercode", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);
            dbCommand = db.GetSqlStringCommand(Sql);
            DataSet ds2 = db.ExecuteDataSet(dbCommand);
            ds2.Tables[0].PrimaryKey = new DataColumn[] { ds2.Tables[0].Columns["客户代码"] };

            DataTable tmpDt = new DataTable();
            tmpDt.Columns.Add("客户代码", typeof(int));
            tmpDt.Columns.Add("客户名称", typeof(string));
            tmpDt.Columns.Add("人数", typeof(int));
            tmpDt.Columns.Add("小于10人团数", typeof(int));
            tmpDt.Columns.Add("大于10人团数", typeof(int));

            if (ds1.Tables.Count > 0)
            {
                foreach (DataRow dr1 in ds1.Tables[0].Rows)
                {
                    DataRow newDr = tmpDt.NewRow();
                    newDr["客户代码"] = dr1["客户代码"];
                    newDr["客户名称"] = dr1["客户名称"];
                    newDr["人数"] = dr1["总人数"];
                    if (ds2.Tables.Count > 0 && ds2.Tables[0].Rows.Contains(dr1["客户代码"]))
                    {
                        DataRow dr2 = ds2.Tables[0].Rows.Find(dr1["客户代码"]);
                        newDr["大于10人团数"] = dr2["大于10人团数"];
                        newDr["小于10人团数"] = int.Parse(dr1["总团数"].ToString()) - int.Parse(dr2["大于10人团数"].ToString());
                    }
                    else
                    {
                        newDr["大于10人团数"] = 0;
                        newDr["小于10人团数"] = dr1["总团数"];
                    }
                    tmpDt.Rows.Add(newDr);
                }
            }
            return tmpDt;

            
        }

        /// <summary>
        /// 客户分析
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public static DataTable CustomerAnalysis(DateTime startDate, DateTime endDate)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"   SELECT customercode as 客户代码,customername as 客户名称, SUM(AdultNum) as 人数,COUNT(customercode)as 团数,SUM(SaleTotalCost)as 预算金额,SUM(BudgeTotalPrice)as 审核金额,SUM(RealTotalPrice) as 实收金额,SUM(BudgeTotalPrice)-SUM(RealTotalPrice) as 欠款,SUM(BudgeTotalPrice)-SUM(BudgeTotalCost) as 利润 
                                            FROM orgt_tourgroup 
                                            WHERE StartDate BETWEEN '{0}' AND '{1}' AND TourgroupState<>'{2}' AND TourgroupState<>'{3}' GROUP BY customername,customercode", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 统计所有客户的信息，部分时间段
        /// </summary>
        /// <returns></returns>
        public static DataTable CustomerAnalysis()
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"   SELECT customercode as 客户代码,customername as 客户名称, SUM(AdultNum) as 人数,COUNT(customercode)as 团数,SUM(BudgeTotalPrice)as 审核价格,SUM(BudgeTotalPrice)-SUM(BudgeTotalCost) as 审核利润 
                                            FROM orgt_tourgroup 
                                            WHERE TourgroupState<>'{0}' AND TourgroupState<>'{1}' GROUP BY customername,customercode", TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public static DataTable CustomerAnalysisDetails(string customerCode, DateTime startDate, DateTime endDate)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"   SELECT Customername ,Tourgroupname ,TourgroupID ,Country ,AdultNum ,SaleTotalCost , BudgeTotalPrice ,RealTotalPrice ,(BudgeTotalPrice-RealTotalPrice) as ownPrice,TourgroupState FROM orgt_tourgroup
                                            WHERE Customercode='{0}' and StartDate BETWEEN '{1}' AND '{2}' AND TourgroupState<>'{3}' AND TourgroupState<>'{4}' ", customerCode, startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion


        #region 供应商情况统计

        internal static DataTable ProviderDisperseAnalysis(DateTime dateStart, DateTime dateEnd)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = "";
            Sql = string.Format(@"      SELECT e.ProviderID as 供应商ID,e.ProviderName as 供应商名称,count(t.TourgroupID) as 团数 
                                        FROM dbo.fee_Expense e INNER JOIN dbo.orgt_TourGroup t ON e.TourgroupID = t.TourgroupID INNER JOIN dbo.bas_Provider p ON e.ProviderID = p.ProviderID
                                        WHERE t.StartDate BETWEEN '{0}' AND '{1}' and TourgroupState<>'{2}' AND TourgroupState<>'{3}' 
                                        GROUP BY e.ProviderID,e.ProviderName", dateStart.ToString("yyyy-MM-dd"), dateEnd.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        internal static DataTable ProviderDisperseAnalysisDetails(string providerId,DateTime dateStart, DateTime dateEnd, bool thanTen)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = "";
            if (thanTen)
                Sql = string.Format(@"  SELECT  e.ProviderID as 供应商ID,e.ProviderName as 供应商名称,count(t.TourgroupID) as 大于10人团数 
                                        FROM dbo.fee_Expense e INNER JOIN dbo.orgt_TourGroup t ON e.TourgroupID = t.TourgroupID INNER JOIN dbo.bas_Provider p ON e.ProviderID = p.ProviderID
                                        WHERE e.ProviderID={0} AND t.AdultNum>=10 AND t.StartDate BETWEEN '{1}' AND '{2}' and TourgroupState<>'{3}' AND TourgroupState<>'{4}' 
                                        GROUP BY e.ProviderID,e.ProviderName", providerId, dateStart.ToString("yyyy-MM-dd"), dateEnd.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);
            else
                Sql = string.Format(@"  SELECT  e.ProviderID as 供应商ID,e.ProviderName as 供应商名称,count(t.TourgroupID) as 小于10人团数 
                                        FROM dbo.fee_Expense e INNER JOIN dbo.orgt_TourGroup t ON e.TourgroupID = t.TourgroupID INNER JOIN dbo.bas_Provider p ON e.ProviderID = p.ProviderID
                                        WHERE e.ProviderID={0} AND t.AdultNum<10 AND t.StartDate BETWEEN '{1}' AND '{2}' and TourgroupState<>'{3}' AND TourgroupState<>'{4}' 
                                        GROUP BY e.ProviderID,e.ProviderName", providerId, dateStart.ToString("yyyy-MM-dd"), dateEnd.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        
        internal static DataTable ProviderYearAnalysis(string providerId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = "";
            Sql = string.Format(@"      SELECT datepart(year,[StartDate]) as 年份,sum(t.AdultNum) as 人数 
                                        FROM dbo.fee_Expense e INNER JOIN dbo.orgt_TourGroup t ON e.TourgroupID = t.TourgroupID INNER JOIN dbo.bas_Provider p ON e.ProviderID = p.ProviderID
                                        WHERE e.ProviderID={0} AND TourgroupState<>'{1}' AND TourgroupState<>'{2}' 
                                        GROUP BY datepart(year,[StartDate]) ORDER BY 年份", providerId, TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        
        public static DataTable ProviderBusinessProportionAnalysis(DateTime startDate, DateTime endDate)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = "";
            Sql = string.Format(@"    SELECT  p.City as 城市,count(t.TourgroupID) as 团数, sum(t.AdultNum) as 人数 
                                        FROM dbo.fee_Expense e INNER JOIN dbo.orgt_TourGroup t ON e.TourgroupID = t.TourgroupID INNER JOIN dbo.bas_Provider p ON e.ProviderID = p.ProviderID
                                        WHERE t.StartDate BETWEEN '{0}' AND '{1}' AND t.TourgroupState<>'{2}' AND  t.TourgroupState<>'{3}'
                                        GROUP BY p.City  ORDER BY 城市", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public static DataTable ProviderBusinessProportionAnalysisDetails(DateTime startDate, DateTime endDate, string cityName,ProviderTypeEnum providerType)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = "";
            switch (providerType)
            {
                case ProviderTypeEnum.宾馆:
                    Sql = string.Format(@"  SELECT e.ProviderName as 名称, sum(t.AdultNum) as 人数 
                                                FROM dbo.fee_Expense e INNER JOIN dbo.orgt_TourGroup t ON e.TourgroupID = t.TourgroupID INNER JOIN dbo.bas_Provider p ON e.ProviderID = p.ProviderID
                                                WHERE p.City='{0}' AND p.ProviderType='{1}'
                                                GROUP BY e.ProviderName ORDER BY 人数 DESC", cityName, ProviderTypeEnum.宾馆, startDate.ToString("yyyy-MM-dd"));
                    break;
                case ProviderTypeEnum.旅行社:
                    Sql = string.Format(@"  SELECT e.ProviderName as 名称, sum(t.AdultNum) as 人数 
                                                FROM dbo.fee_Expense e INNER JOIN dbo.orgt_TourGroup t ON e.TourgroupID = t.TourgroupID INNER JOIN dbo.bas_Provider p ON e.ProviderID = p.ProviderID
                                                WHERE p.City='{0}' AND p.ProviderType='{1}' 
                                                GROUP BY e.ProviderName ORDER BY 人数 DESC", cityName, ProviderTypeEnum.旅行社);
                    break;
                case ProviderTypeEnum.汽车:
                    Sql = string.Format(@"  SELECT e.ProviderName as 名称, sum(t.AdultNum) as 人数 
                                                FROM dbo.fee_Expense e INNER JOIN dbo.orgt_TourGroup t ON e.TourgroupID = t.TourgroupID INNER JOIN dbo.bas_Provider p ON e.ProviderID = p.ProviderID
                                                WHERE p.City='{0}' AND p.ProviderType='{1}' 
                                                GROUP BY e.ProviderName ORDER BY 人数 DESC", cityName, ProviderTypeEnum.汽车);
                    break;
                case ProviderTypeEnum.餐厅:
                    Sql = string.Format(@"  SELECT e.ProviderName as 名称, sum(t.AdultNum) as 人数 
                                                FROM dbo.fee_Expense e INNER JOIN dbo.orgt_TourGroup t ON e.TourgroupID = t.TourgroupID INNER JOIN dbo.bas_Provider p ON e.ProviderID = p.ProviderID
                                                WHERE p.City='{0}' AND p.ProviderType='{1}'
                                                GROUP BY e.ProviderName ORDER BY 人数 DESC", cityName, ProviderTypeEnum.餐厅);
                    break;
                default:
                    break;
            }
//            Sql = string.Format(@"  SELECT e.TypeBelong as 类型, sum(t.AdultNum) as 人数 
//                                        FROM dbo.fee_Expense e INNER JOIN dbo.orgt_TourGroup t ON e.TourgroupID = t.TourgroupID INNER JOIN dbo.bas_Provider p ON e.ProviderID = p.ProviderID
//                                        WHERE p.City='{0}' AND (e.TypeBelong='{1}' OR e.TypeBelong='{2}' OR e.TypeBelong='{3}' OR e.TypeBelong='{4}') AND StartDate BETWEEN '{5}' AND '{6}' AND t.TourgroupState<>'{7}' AND  t.TourgroupState<>'{8}'
//                                        GROUP BY e.TypeBelong", cityName, ProviderTypeEnum.旅行社, ProviderTypeEnum.宾馆, ProviderTypeEnum.餐厅, ProviderTypeEnum.汽车, startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);
            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        
        /// <summary>
        /// 供应商分析
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public static DataTable ProviderAnalysis(DateTime startDate, DateTime endDate)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = "";
            Sql = string.Format(@"  SELECT e.ProviderID as 供应商ID, e.ProviderName as 供应商, e.TypeBelong as 类型, p.City as 城市,count(t.TourgroupID) as 团数, sum(t.AdultNum) as 人数, sum(e.Budget) as 应付款, 
                                        sum(e.PayTotal) as 已支付总额, sum(e.ApplyPayTotal) as 已申请总额, (sum(e.ApplyPayTotal)-sum(e.PayTotal)) as 欠款,sum(e.AdjustAmount) as 调整值
                                        FROM dbo.fee_Expense e INNER JOIN dbo.orgt_TourGroup t ON e.TourgroupID = t.TourgroupID INNER JOIN dbo.bas_Provider p ON e.ProviderID = p.ProviderID
                                        WHERE t.StartDate BETWEEN '{0}' AND '{1}' AND t.TourgroupState<>'{2}' AND  t.TourgroupState<>'{3}'
                                        GROUP BY e.ProviderID, e.ProviderName, e.TypeBelong, p.City", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public static DataTable ProviderAnalysisDetails(DateTime startDate, DateTime endDate, string providerId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = "";
            Sql = string.Format(@" SELECT e.ProviderID as 供应商ID, e.ProviderName as 供应商,t.TourgroupID as 团号, t.TourgroupName as 团名, t.Country as 国家,t.AdultNum as 人数,e.Budget as 应付款, 
                                          e.PayTotal as 已支付总额, e.ApplyPayTotal as 已申请总额, (e.ApplyPayTotal-e.PayTotal) as 欠款,e.AdjustAmount as 调整值,t.TourgroupState as 状态 
                                          FROM dbo.fee_Expense e INNER JOIN dbo.orgt_TourGroup t ON e.TourgroupID = t.TourgroupID 
                                          WHERE  t.StartDate BETWEEN '{0}' AND '{1}' AND e.ProviderID={2}  AND t.TourgroupState<>'{3}' AND  t.TourgroupState<>'{4}'", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), providerId, TeamStateEnum.取消, TeamStateEnum.删除);
            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        #endregion

        #region 销售统计

        public static DataTable SaleSinglePersonAnalysis(string ownerA)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"   SELECT	sum(AdultNum) as 人数,COUNT(OwnerA) as 团数,datepart(year,[StartDate]) as 年份,
		                                    sum(BudgeTotalPrice) as 审核价格, sum(BudgeTotalPrice)-sum(BudgeTotalCost) as 审核利润
                                            FROM orgt_tourgroup  
                                            WHERE OwnerA='{0}' AND TourgroupState<>'{1}' AND TourgroupState<>'{2}' GROUP BY datepart(year,[StartDate]) ORDER BY 年份", ownerA, TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 客户分析
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public static DataTable SaleAnalysis(string ownerA, DateTime startDate, DateTime endDate,string customerId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string szCondition = "";
            string Sql = "";

            if (ownerA != "")
                szCondition = " OwnerA='" + ownerA + "' AND";

            if (szCondition != "")
            {
                if (customerId != "")
                    szCondition += " CustomerCode='" + customerId + "' AND ";
            }
            else
            {
                if (customerId != "")
                    szCondition += " CustomerCode='" + customerId + "' AND ";
            }
            Sql = string.Format(@"   SELECT OwnerA as 名称, SUM(AdultNum) as 人数,COUNT(OwnerA) as 团数,
                                            SUM(SaleTotalPrice)as 预算价格,SUM(SaleTotalCost)as 预算成本,(SUM(SaleTotalPrice)-SUM(SaleTotalCost)) as 预算利润,
                                            SUM(BudgeTotalPrice)as 审核价格,SUM(BudgeTotalCost)as 审核成本,(SUM(BudgeTotalPrice)-SUM(BudgeTotalCost)) as 审核利润,
                                            SUM(RealTotalPrice) as 实际价格,SUM(RealTotalCost) as 实际成本,SUM(RealTotalPrice)-SUM(RealTotalCost) as 实际利润,
                                            ((SUM(BudgeTotalPrice)-SUM(BudgeTotalCost))-(SUM(RealTotalPrice)-SUM(RealTotalCost)))as 利润差额 
                                            FROM orgt_tourgroup 
                                            WHERE {4} StartDate BETWEEN '{0}' AND '{1}' AND TourgroupState<>'{2}' AND TourgroupState<>'{3}' GROUP BY OwnerA", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除, szCondition);

//            if (ownerA == "")
//                Sql = string.Format(@"   SELECT OwnerA as 名称, SUM(AdultNum) as 人数,COUNT(OwnerA) as 团数,
//                                            SUM(SaleTotalPrice)as 预算价格,SUM(SaleTotalCost)as 预算成本,(SUM(SaleTotalPrice)-SUM(SaleTotalCost)) as 预算利润,
//                                            SUM(BudgeTotalPrice)as 审核价格,SUM(BudgeTotalCost)as 审核成本,(SUM(BudgeTotalPrice)-SUM(BudgeTotalCost)) as 审核利润,
//                                            SUM(RealTotalPrice) as 实际价格,SUM(RealTotalCost) as 实际成本,SUM(RealTotalPrice)-SUM(RealTotalCost) as 实际利润,
//                                            ((SUM(BudgeTotalPrice)-SUM(BudgeTotalCost))-(SUM(RealTotalPrice)-SUM(RealTotalCost)))as 利润差额 
//                                            FROM orgt_tourgroup 
//                                            WHERE StartDate BETWEEN '{0}' AND '{1}' AND TourgroupState<>'{2}' AND TourgroupState<>'{3}' AND CustomerCode='{4}' GROUP BY OwnerA", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除, customerId);
//            else
//                Sql = string.Format(@"   SELECT OwnerA as 名称, SUM(AdultNum) as 人数,COUNT(OwnerA) as 团数,
//                                            SUM(SaleTotalPrice)as 预算价格,SUM(SaleTotalCost)as 预算成本,(SUM(SaleTotalPrice)-SUM(SaleTotalCost)) as 预算利润,
//                                            SUM(BudgeTotalPrice)as 审核价格,SUM(BudgeTotalCost)as 审核成本,(SUM(BudgeTotalPrice)-SUM(BudgeTotalCost)) as 审核利润,
//                                            SUM(RealTotalPrice) as 实际价格,SUM(RealTotalCost) as 实际成本,SUM(RealTotalPrice)-SUM(RealTotalCost) as 实际利润,
//                                            ((SUM(BudgeTotalPrice)-SUM(BudgeTotalCost))-(SUM(RealTotalPrice)-SUM(RealTotalCost)))as 利润差额                                             FROM orgt_tourgroup 
//                                            WHERE OwnerA='{0}' AND StartDate BETWEEN '{1}' AND '{2}' AND TourgroupState<>'{3}' AND TourgroupState<>'{4}' AND CustomerCode='{5}' GROUP BY OwnerA", ownerA, startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除, customerId);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable SaleAnalysisDetails(string ownerA, DateTime startDate, DateTime endDate,string customerId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string szCondition = "";
            string Sql = "";

            if (ownerA != "")
                szCondition = " OwnerA='" + ownerA + "' AND ";

            if (szCondition != "")
            {
                if (customerId != "")
                    szCondition += " CustomerCode='" + customerId + "' AND ";
            }
            else
            {
                if (customerId != "")
                    szCondition += " CustomerCode='" + customerId + "' AND ";
            }
            Sql = string.Format(@"   SELECT OwnerA as 名称,Tourgroupname as 团名,TourgroupID as 团号,AdultNum as 人数, 
                                            SaleTotalPrice as 预算价格, SaleTotalCost as 预算成本,(SaleTotalPrice-SaleTotalCost) as 预算利润,
                                            BudgeTotalPrice as 审核价格, BudgeTotalCost as 审核成本,(BudgeTotalPrice-BudgeTotalCost) as 审核利润,
                                            RealTotalPrice as 实际收入,RealTotalCost as 实际成本,(RealTotalPrice-RealTotalCost) as 实际利润,
                                            ((BudgeTotalPrice-BudgeTotalCost)-(RealTotalPrice-RealTotalCost))as 利润差额, 
                                            TourgroupState as 团状态 FROM orgt_tourgroup
                                            WHERE {0} StartDate BETWEEN '{1}' AND '{2}' AND TourgroupState<>'{3}' AND TourgroupState<>'{4}'",szCondition, startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);


//            string Sql = string.Format(@"   SELECT OwnerA as 名称,Tourgroupname as 团名,TourgroupID as 团号,AdultNum as 人数, 
//                                            SaleTotalPrice as 预算价格, SaleTotalCost as 预算成本,(SaleTotalPrice-SaleTotalCost) as 预算利润,
//                                            BudgeTotalPrice as 审核价格, BudgeTotalCost as 审核成本,(BudgeTotalPrice-BudgeTotalCost) as 审核利润,
//                                            RealTotalPrice as 实际收入,RealTotalCost as 实际成本,(RealTotalPrice-RealTotalCost) as 实际利润,
//                                            ((BudgeTotalPrice-BudgeTotalCost)-(RealTotalPrice-RealTotalCost))as 利润差额, 
//                                            TourgroupState as 团状态 FROM orgt_tourgroup
//                                            WHERE OwnerA='{0}' and StartDate BETWEEN '{1}' AND '{2}' AND TourgroupState<>'{3}' AND TourgroupState<>'{4}' AND CustomerCode='{5}'", ownerA, startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除, customerId);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

        #region 旅行团月统计表
        public static DataTable TourgroupAnalysisForMonth(string year)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string Sql = string.Format(@"   SELECT	datepart(month,StartDate) as 月份, COUNT(*) as 团数, SUM(AdultNum) as 人数,SUM(StayOver)as 天数,
                                            SUM(SaleTotalPrice)as 预算价格,SUM(SaleTotalCost)as 预算成本,(SUM(SaleTotalPrice)-SUM(SaleTotalCost)) as 预算利润,(CASE WHEN SUM(SaleTotalPrice)> 0 THEN (SUM(SaleTotalPrice)-SUM(SaleTotalCost))/SUM(SaleTotalPrice) ELSE 0 END) as 预算毛利率,
                                            SUM(BudgeTotalPrice)as 审核价格,SUM(BudgeTotalCost)as 审核成本,(SUM(BudgeTotalPrice)-SUM(BudgeTotalCost)) as 审核利润,(CASE WHEN SUM(BudgeTotalPrice)> 0 THEN (SUM(BudgeTotalPrice)-SUM(BudgeTotalCost))/SUM(BudgeTotalPrice) ELSE 0 END) as 审核毛利率,
                                            SUM(RealTotalPrice) as 实际价格,SUM(RealTotalCost) as 实际成本,SUM(RealTotalPrice)-SUM(RealTotalCost) as 实际利润,(CASE WHEN SUM(RealTotalPrice)> 0 THEN (SUM(BudgeTotalPrice)-SUM(RealTotalCost))/SUM(RealTotalPrice) ELSE 0 END) as 实际毛利率
                                            FROM dbo.orgt_TourGroup WHERE datepart(year,StartDate)='{0}' AND TourgroupState<>'{1}' AND TourgroupState<>'{2}'
                                            GROUP BY datepart(month,StartDate) ORDER BY datepart(month,StartDate)", year, TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);

            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public static DataTable TourgroupAnalysisForYear()
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string Sql = string.Format(@"   SELECT	datepart(year,StartDate) as 年份, COUNT(*) as 团数, SUM(AdultNum) as 人数,
                                            SUM(BudgeTotalPrice)as 审核价格,(SUM(BudgeTotalPrice)-SUM(BudgeTotalCost)) as 审核利润
                                            FROM dbo.orgt_TourGroup WHERE TourgroupState<>'{0}' AND TourgroupState<>'{1}'
                                            GROUP BY datepart(year,StartDate)  ORDER BY  年份", TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);

            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }
        #endregion

        #region 导游分析
        internal static DataTable GuideAnalysis(string ownerA, string customerCode, DateTime dateStart, DateTime dateEnd)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string szCondition = "";

            if (ownerA != "")
                szCondition = "AND t.OwnerA='" + ownerA + "'";

            if (szCondition != "")
            {
                if (customerCode != "")
                    szCondition += " AND t.CustomerCode='" + customerCode + "'";
            }
            else
            {
                if (customerCode != "")
                    szCondition += "AND t.CustomerCode='" + customerCode + "'";
            }

            string Sql = string.Format(@"   SELECT	CiceroniName as 导游,CiceroniID,COUNT(OwnerA) as 团数,sum(AdultNum) as 人数
		                                    FROM cic_GuideArrange g,orgt_tourgroup t  
                                            WHERE g.TourgroupID=t.TourgroupID {0} AND g.StartDate BETWEEN '{1}' AND '{2}' AND TourgroupState<>'{3}' AND TourgroupState<>'{4}'
                                            GROUP BY CiceroniName,CiceroniID", szCondition, dateStart.ToString("yyyy-MM-dd"), dateEnd.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);

            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        internal static DataTable GuideTourgroupAnalysisDetail(string ownerA, string customerCode,string ciceroniId, DateTime dateStart, DateTime dateEnd)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string szCondition = "";

            if (ownerA != "")
                szCondition = "AND t.OwnerA='" + ownerA + "'";

            if (szCondition != "")
            {
                if (customerCode != "")
                    szCondition += " AND t.CustomerCode='" + customerCode + "'";
            }
            else
            {
                if (customerCode != "")
                    szCondition += "AND t.CustomerCode='" + customerCode + "'";
            }
            string Sql = string.Format(@"   SELECT	t.TourgroupID,t.TourgroupName,t.AdultNum,t.StartDate
		                                    FROM cic_GuideArrange g,orgt_tourgroup t  
                                            WHERE g.TourgroupID=t.TourgroupID {0} AND g.CiceroniId={1} AND g.StartDate BETWEEN '{2}' AND '{3}' AND TourgroupState<>'{4}' AND TourgroupState<>'{5}'
                                            ", szCondition, ciceroniId, dateStart.ToString("yyyy-MM-dd"), dateEnd.ToString("yyyy-MM-dd"), TeamStateEnum.取消, TeamStateEnum.删除);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);

            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        } 
        #endregion

        #region 机票统计分析
        public static DataTable PlanePriceAnalysisForMonth(string year,string beginPosition,string endPosition,string type)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string Sql;

            switch (type)
	        {
                case "团队":
                    Sql = string.Format(@"   SELECT  datepart(month,StartDate) as 月份,sum(AdultUnitPrice)/count(*) as 票价  FROM fee_FlightOrder 
                                             WHERE datepart(year,StartDate)={0} AND (BeginPosition='{1}' AND EndPosition='{2}') AND AdultNum>=10   
                                             GROUP BY datepart(month,StartDate) ORDER BY datepart(month,StartDate)", year, beginPosition, endPosition);
                    break;
                case "散客":
                    Sql = string.Format(@"   SELECT  datepart(month,StartDate) as 月份,sum(AdultUnitPrice)/count(*) as 票价 FROM fee_FlightOrder 
                                             WHERE datepart(year,StartDate)={0} AND (BeginPosition='{1}' AND EndPosition='{2}') AND AdultNum<10   
                                             GROUP BY  datepart(month,StartDate) ORDER BY datepart(month,StartDate)", year, beginPosition, endPosition);
                    break;
		        default:
                    Sql = string.Format(@"   SELECT  datepart(month,StartDate) as 月份,sum(AdultUnitPrice)/count(*) as 票价 FROM fee_FlightOrder 
                                             WHERE datepart(year,StartDate)={0} AND (BeginPosition='{1}' AND EndPosition='{2}')   
                                             GROUP BY  datepart(month,StartDate) ORDER BY datepart(month,StartDate)", year, beginPosition, endPosition);
                    break;
	        }

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);

            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public static DataTable PlaneTicketAnalysisForMonth(string year, string beginPosition, string endPosition, string type)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string Sql;

            switch (type)
            {
                case "团队":
                    Sql = string.Format(@"   SELECT  datepart(month,StartDate) as 月份,COUNT(AdultNum) as 票数
                                             FROM fee_FlightOrder 
                                             WHERE datepart(year,StartDate)={0} AND (BeginPosition='{1}' AND EndPosition='{2}') AND AdultNum>=10   
                                             GROUP BY  datepart(month,StartDate) ORDER BY datepart(month,StartDate)", year, beginPosition, endPosition);
                    break;
                case "散客":
                    Sql = string.Format(@"   SELECT  datepart(month,StartDate) as 月份,COUNT(AdultNum) as 票数
                                             FROM fee_FlightOrder 
                                             WHERE datepart(year,StartDate)={0} AND (BeginPosition='{1}' AND EndPosition='{2}') AND AdultNum<10   
                                             GROUP BY  datepart(month,StartDate) ORDER BY datepart(month,StartDate)", year, beginPosition, endPosition);
                    break;
                default:
                    Sql = string.Format(@"   SELECT  datepart(month,StartDate) as 月份,COUNT(AdultNum) as 票数
                                             FROM fee_FlightOrder 
                                             WHERE datepart(year,StartDate)={0} AND (BeginPosition='{1}' AND EndPosition='{2}')   
                                             GROUP BY datepart(month,StartDate) ORDER BY datepart(month,StartDate)", year, beginPosition, endPosition);
                    break;
            } dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);

            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }
        //辅助函数
        public static DataTable GetPlaneBeginAndEndPosition(string postionType)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string Sql = "";
            switch (postionType)
            {
                case "BeginPosition":
                    Sql = @"   SELECT  beginposition
                              FROM fee_FlightOrder 
                              GROUP BY  beginposition";
                    break;
                case "EndPosition":
                    Sql = @"   SELECT endposition
                              FROM fee_FlightOrder 
                              GROUP BY  endposition";
                    break;
                default:
                    break;
            }


            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);

            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion
    }
}
